package xls;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.Locale;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.DateFormats;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

class WriteXls {
	public static void main(String[] args) {
		try {
			String filename = "C:\\XLS\\test.xls";
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), ws);
			//create sheets
			
	
			WritableSheet sheet = workbook.createSheet("Pass Starting point", 0);
			writeDataSheet(sheet);
			workbook.write();
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	private static void writeDataSheet(WritableSheet sheet) throws WriteException {
		try {
			Label Number = new Label(0,0,"Number");	
			Label LatLngX = new Label(1,0,"LatLngX");
			Label LatLngY = new Label(2,0,"LatLngY");
			Label passName = new Label(3,0,"passName");
			Label NameEN = new Label(4,0,"NameEN");
			Label NameDE = new Label(5,0,"NameDE");
			Label NameNL = new Label(6,0,"NameNL");
			Label NameIT = new Label(7,0,"NameIT");
			Label NameFR = new Label(8,0,"NameFR");
			Label SiteName = new Label(9,0,"SiteName");
			Label passDescription = new Label(10,0,"passDescription");
			Label DescriptionEN = new Label(11,0,"DescriptionEN");
			Label DescriptionDE = new Label(12,0,"DescriptionDE");
			Label DescriptionNL = new Label(13,0,"DescriptionNL");
			Label DescriptionIT = new Label(14,0,"DescriptionIT");
			Label DescriptionFR = new Label(15,0,"DescriptionFR");
			Label LinkRoute = new Label(16,0,"LinkRoute");
			Label WebElement = new Label(17,0,"WebElement");
			Label WebElementEN = new Label(18,0,"WebElementEN");
			Label WebElementDE = new Label(19,0,"WebElementDE");
			Label WebElementNL = new Label(20,0,"WebElementNL");
			Label WebElementIT = new Label(21,0,"WebElementIT");
			Label WebElementFR = new Label(22,0,"WebElementFR");
			Label LinkBikemap = new Label(23,0,"LinkBikemap");
			Label LinkStreetview = new Label(24,0,"LinkStreetview");
			Label LinkMotorbike = new Label(25,0,"LinkMotorbike");
			Label ds = new Label(26,0,"ds");
			Label LinkClimbbybike = new Label(27,0,"LinkClimbbybike");
			Label LinkQuaeldich = new Label(28,0,"LinkQuaeldich");
			Label LinkVideo = new Label(29,0,"LinkVideo");
			Label LinkSlideshow = new Label(30,0,"LinkSlideshow");
			Label LinkCyclingChallenge = new Label(31,0,"LinkCyclingChallenge");
			Label Link3Dtour = new Label(32,0,"Link3Dtour");
			Label LinkKlimgeiten = new Label(33,0,"LinkKlimgeiten");
			Label passLink = new Label(34,0,"passLink");
			Label HyperlinkEN = new Label(35,0,"HyperlinkEN");
			Label HyperlinkDE = new Label(36,0,"HyperlinkDE");
			Label HyperlinkNL = new Label(37,0,"HyperlinkNL");
			Label HyperlinkIT = new Label(38,0,"HyperlinkIT");
			Label HyperlinkFR = new Label(39,0,"HyperlinkFR");
			Label UtilsPass = new Label(40,0,"UtilsPass");
			Label UtilsTour = new Label(41,0,"UtilsTour");
			Label UtilsVideo = new Label(42,0,"UtilsVideo");
			Label UtilsSlideShow = new Label(43,0,"UtilsSlideShow");
			Label UtilsConversation = new Label(44,0,"UtilsConversation");
			Label passOracle = new Label(45,0,"passOracle");
			Label OracleEN = new Label(46,0,"OracleEN");
			Label OracleDE = new Label(47,0,"OracleDE");
			Label OracleNL = new Label(48,0,"OracleNL");
			Label OracleIT = new Label(49,0,"OracleIT");
			Label OracleFR = new Label(50,0,"OracleFR");
			Label LatLngXStart = new Label(51,0,"LatLngXStart");
			Label LatLngYStart = new Label(52,0,"LatLngYStart");
			Label LatLngXEnd = new Label(53,0,"LatLngXEnd");
			Label LatLngYEnd = new Label(54,0,"LatLngYEnd");
			Label passStreetview = new Label(55,0,"passStreetview");
			Label StreetviewTour = new Label(56,0,"StreetviewTour");

			sheet.addCell(Number);
			sheet.addCell(LatLngX);
			sheet.addCell(LatLngY);
			sheet.addCell(passName);
			sheet.addCell(NameEN);
			sheet.addCell(NameDE);
			sheet.addCell(NameNL);
			sheet.addCell(NameIT);
			sheet.addCell(NameFR);
			sheet.addCell(SiteName);
			sheet.addCell(passDescription);
			sheet.addCell(DescriptionEN);
			sheet.addCell(DescriptionDE);
			sheet.addCell(DescriptionNL);
			sheet.addCell(DescriptionIT);
			sheet.addCell(DescriptionFR);
			sheet.addCell(LinkRoute);
			sheet.addCell(WebElement);
			sheet.addCell(WebElementEN);
			sheet.addCell(WebElementDE);
			sheet.addCell(WebElementNL);
			sheet.addCell(WebElementIT);
			sheet.addCell(WebElementFR);
			sheet.addCell(LinkBikemap);
			sheet.addCell(LinkStreetview);
			sheet.addCell(LinkMotorbike);
			sheet.addCell(ds);
			sheet.addCell(LinkClimbbybike);
			sheet.addCell(LinkQuaeldich);
			sheet.addCell(LinkVideo);
			sheet.addCell(LinkSlideshow);
			sheet.addCell(LinkCyclingChallenge);
			sheet.addCell(Link3Dtour);
			sheet.addCell(LinkKlimgeiten);
			sheet.addCell(passLink);
			sheet.addCell(HyperlinkEN);
			sheet.addCell(HyperlinkDE);
			sheet.addCell(HyperlinkNL);
			sheet.addCell(HyperlinkIT);
			sheet.addCell(HyperlinkFR);
			sheet.addCell(UtilsPass);
			sheet.addCell(UtilsTour);
			sheet.addCell(UtilsVideo);
			sheet.addCell(UtilsSlideShow);
			sheet.addCell(UtilsConversation);
			sheet.addCell(passOracle);
			sheet.addCell(OracleEN);
			sheet.addCell(OracleDE);
			sheet.addCell(OracleNL);
			sheet.addCell(OracleIT);
			sheet.addCell(OracleFR);
			sheet.addCell(LatLngXStart);
			sheet.addCell(LatLngYStart);
			sheet.addCell(LatLngXEnd);
			sheet.addCell(LatLngYEnd);
			sheet.addCell(passStreetview);
			sheet.addCell(StreetviewTour);			
			
			
			
	    	Workbook workbookLatlng = Workbook.getWorkbook(new File("C:\\XLS\\Latlngs.xls"));
	    	String latLngnew = "";
	    	int j = 1;
	    	int k = 1;
	    	Sheet sheetLatlng = workbookLatlng.getSheet(0);
	    	Cell[] name = sheetLatlng.getColumn(0);
	    	FileWriter fstream = new FileWriter("C:\\XLS\\latlngRewritten.txt");
	    	BufferedWriter textFile = new BufferedWriter(fstream);
	    	for (int i = 1; i < name.length; i++) {
		        Cell latlngCell = sheetLatlng.getCell(0,i);
		        String latLng = latlngCell.getContents();
		        System.out.println(latLng);
		        
		        if (!latLng.startsWith("//")) {
		        	latLngnew = latLng.replaceAll(".*= ", "").replaceAll(";","");
		        	
		        }
		        i++;
		        i++;
		        
		        Label l = new Label(1,j,latLngnew);
		        j++;
		        sheet.addCell(l);
	    	}
			
	    	for (int i = 2; i < name.length; i++) {
		        Cell latlngCell = sheetLatlng.getCell(0,i);
		        String latLng = latlngCell.getContents();
		        System.out.println(latLng);
		        
		        if (!latLng.startsWith("//")) {
		        	latLngnew = latLng.replaceAll(".*= ", "").replaceAll(";","");
		        	
		        }
		        i++;
		        i++;
		        
		        Label l = new Label(2,k,latLngnew);
		        k++;
		        sheet.addCell(l);
	    	}
			
			
			
			
			Workbook workbook = Workbook.getWorkbook(new File("C:\\XLS\\CTA.xls"));
			Sheet sheetWorkingBook = workbook.getSheet(5);
			Cell[] count = sheetWorkingBook.getColumn(0);
			for (int i = 1; i < count.length; i++) {
				Cell passNameENCell = sheetWorkingBook.getCell(4, i);
				String passNameEN = passNameENCell.getContents();
				System.out.println(passNameEN);
				String replaced = Utils.replaceUmlautsHtml(passNameEN);
				String heightremoved = replaced.substring(0, replaced.length() - 8);
				String hyphenated = heightremoved.replaceAll(" ", "%20");
				Label l = new Label(19,i,
						"<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Conversation%20"
								+ hyphenated + "' scrolling='no' allowtransparency='true'></iframe>");
				sheet.addCell(l);
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	private static void writeImageSheet(WritableSheet s) throws WriteException {
		/* Creates Label and writes image to one cell of sheet */
		Label l = new Label(0, 0, "Image");
		s.addCell(l);
		// WritableImage wi = new WritableImage(0, 3, 5, 7, new
		// File("image.png"));
		// s.addImage(wi);

		/* Creates Label and writes hyperlink to one cell of sheet */
		l = new Label(0, 15, "HYPERLINK");
		s.addCell(l);
		Formula f = new Formula(1, 15, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", " + "\"JExcelApi Home Page\")");
		s.addCell(f);

	}
}